﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data.Common;

namespace ImportArticles.Process
{
    public class UploadArticlesIntoDb
    {
        int Arch_CODEIndex = 0;
        int Arch_NAMEIndex = 0;     

        public decimal StartLineNumber = 0;
        public decimal MaxLinesToCommit = 100000; // cada este numero de lineas commitea y reconecta        

        public UploadArticlesIntoDb()
        {
            int i = 0;
            Arch_CODEIndex = i++;
            Arch_NAMEIndex = i++;           
        }

        public string InputFilePath { get; set; }

        public string ConnectionString { get; set; }

        public int EmpresaId { get; set; }

        public delegate void Notificar(string mensaje);

        public event Notificar Notify;

        protected void OnNotificar(string mensaje)
        {
            if (Notify != null)
            {
                Notify(mensaje);
            }
        }

        public void DoImport()
        {
            int i = 0;

            //CurrentLine = 0;
            bool keepGoing = true;     
            decimal currentLineNumber = StartLineNumber;

            // Se corta la conexion y da timeout seguido
            while (keepGoing)
            {
                try
                {
                    using (DbConnection conn = new SqlConnection(ConnectionString))
                    {
                        OnNotificar("Abriendo conexión a la base");
                        conn.Open();

                        EjecutarComandoInsert(ref i, ref currentLineNumber, conn, ref keepGoing);

                        conn.Close();
                    }
                }
                catch (Exception ex)
                {
                    OnNotificar(string.Format("Linea Archivo: {0}", currentLineNumber));
                    string message = GetExceptionMessage(ex);
                    string stack = GetExceptionStackTrace(ex);

                    OnNotificar(message + stack);
                   
                    keepGoing = false;                    
                }
            }
        }

        private void EjecutarComandoInsert(ref int i, ref decimal currentLineNumber, DbConnection conn, ref bool keepGoing)
        {
            string line;
            DbTransaction dbTransaction = null;

            string lineOfFileForException = string.Empty;
            decimal readRow = 0;

            try
            {
                using (DbCommand dbCommand = conn.CreateCommand())
                {
                    dbCommand.Transaction = dbTransaction;
                    dbCommand.CommandText = " if not exists (select 1 from Producto where Codigo = @Codigo and EmpresaId = @EmpresaId) " +
                                            " begin insert into Producto ([Codigo],[Nombre],[EmpresaId]) values (@Codigo, @Nombre, @EmpresaId); end " +
                                            " else begin update Producto set Nombre = @Nombre where Codigo = @Codigo and EmpresaId = @EmpresaId; end ";

                    //dbCommand.Transaction = dbTransaction;                            
                    List<DbParameter> parameterKeys = new List<DbParameter>();

                    #region parametros

                    i = 0;
                    int EmpresaIndex = i++;
                    DbParameter parameter = dbCommand.CreateParameter();
                    parameter.ParameterName = "@EmpresaId";
                    dbCommand.Parameters.Add(parameter);
                    parameterKeys.Add(parameter);

                    int  CodigoIndex = i++;
                    parameter = dbCommand.CreateParameter();
                    parameter.ParameterName = "@Codigo";
                    dbCommand.Parameters.Add(parameter);
                    parameterKeys.Add(parameter);

                    int NombreIndex = i++;
                    parameter = dbCommand.CreateParameter();
                    parameter.ParameterName = "@Nombre";
                    dbCommand.Parameters.Add(parameter);
                    parameterKeys.Add(parameter);
                   
                    #endregion

                    OnNotificar(string.Format("Abriendo arhivo de artículos {0}", InputFilePath));
                    OnNotificar(string.Format("Ejecutando lineas desde {0} ", currentLineNumber));
                    bool comiteado = false;
                    
                    decimal insertedQuantity = 0;
                    using (System.IO.StreamReader file = new System.IO.StreamReader(InputFilePath))
                    {                        
                        while ((line = file.ReadLine()) != null)
                        {
                            if (readRow >= currentLineNumber)
                            {
                                if (dbTransaction == null)
                                {
                                    dbTransaction = conn.BeginTransaction();
                                    dbCommand.Transaction = dbTransaction;
                                }

                                currentLineNumber = readRow;
                                lineOfFileForException = line;
                                string[] dataRow = line.Split(';');

                                parameterKeys[EmpresaIndex].Value = EmpresaId;
                                parameterKeys[CodigoIndex].Value = dataRow[Arch_CODEIndex];
                                parameterKeys[NombreIndex].Value = dataRow[Arch_NAMEIndex];
                                
                                dbCommand.ExecuteNonQuery();
                                insertedQuantity++;
                            }

                            if (insertedQuantity >= MaxLinesToCommit)
                            {
                                if (dbTransaction != null)
                                {
                                    dbTransaction.Commit();
                                }

                                comiteado = true;
                                break;
                            }

                            readRow++;
                        }

                        file.Close();

                        if (!comiteado)
                        {
                            OnNotificar(string.Format("Ejecutando commit. Linea {0}", currentLineNumber));
                            if (dbTransaction != null)
                            {
                                dbTransaction.Commit();
                            }

                            comiteado = true;
                            keepGoing = false;
                        }
                        // tScope.Complete();
                        OnNotificar(string.Format("Se procesaron {0} lineas del archivo", insertedQuantity));
                    }
                }
            }
            catch
            {
                OnNotificar("Ejecutando rollback");
                OnNotificar(string.Format("Fila: {0}", readRow));
                OnNotificar(string.Format("Linea Archivo: {0}", lineOfFileForException));
                if (dbTransaction != null)
                {
                    dbTransaction.Rollback();
                }

                throw;
            }           
        }        

        public static string GetExceptionMessage(Exception ex)
        {
            StringBuilder builder = new StringBuilder();

            Exception current = ex;
            while (current != null)
            {
                builder.Append(current.Message);
                current = current.InnerException;
            }

            return builder.ToString();
        }

        public static string GetExceptionStackTrace(Exception ex)
        {
            StringBuilder builder = new StringBuilder();

            Exception current = ex;
            while (current != null)
            {
                builder.Append(current.StackTrace);
                current = current.InnerException;
            }

            return builder.ToString();
        }

    }
}
